package com.weavernorth.modelInterFace.service;

import com.weavernorth.modelInterFace.pojo.filedEntity;
import com.weavernorth.modelInterFace.pojo.messageVo;
import com.weavernorth.modelInterFace.utils.tableUtils;
import com.weavernorth.sysap.util.SyncUtil;
import com.weavernorth.util.Logger4jUtil;
import net.sf.cglib.asm.$ClassWriter;
import org.apache.log4j.Logger;
import weaver.conn.ConnStatement;
import weaver.conn.EncodingUtils;
import weaver.conn.RecordSet;
import weaver.conn.RecordSetTrans;
import weaver.formmode.setup.ModeRightInfo;
import weaver.general.Util;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static com.platform.external.tooklit.CheckUtil.isNumeric;


/**
 * @Author: bruseewang
 * @Package: com.weavernorth.modelInterFace.service
 * @date: 2020/7/1 16:43
 * @description:
 */
public class modeDaoImpl implements modeDao {
    private Logger log = Logger4jUtil.getLoggerByName("/action/api/OperationData/");

    @Override
    public RecordSet getAllModeData(String strOaTable, String strTbkey) {
        return null;
    }

    @Override
    public String insertData(String strOaTable, List<filedEntity> data, String strTbkey, ConnStatement rs_main_tb, String strCheckBrowser, Map<String, String> mp) {
        String strResult = "0|操作成功！";
        SyncUtil syncUtil = new SyncUtil();
        //获取表的formid
        String strFormid = syncUtil.getModeIdBytableName(strOaTable);
        RecordSetTrans rsTrans = new RecordSetTrans();
        StringBuffer strNameSql = new StringBuffer();
        strNameSql.append("INSERT INTO " + strOaTable + "(  ");
        strNameSql.append("mainKey,formmodeid,modedatacreater,");
        StringBuffer strValSql = new StringBuffer();
        strValSql.append(")VALUES(");
        strValSql.append("'" + strTbkey + "'," + strFormid + ",2,");
        log.info("传入值data数据：" + data.toString());
        try {
            int dataSize = data.size();
            if (dataSize > 0) {
                String strColumnNames = "";
                for (int i = 1; i <= rs_main_tb.getColumnCount(); i++) {
                    String strName = rs_main_tb.getColumnName(i);
                    strColumnNames += strName + ",";
                }
                if (strColumnNames.length() > 0) {
                    strColumnNames = strColumnNames.substring(0, strColumnNames.lastIndexOf(","));
                }
                log.info("数据库列名为：" + strColumnNames);
                for (filedEntity datum : data) {
                    String filedName = datum.getFiledName().toUpperCase();
                    String filedvalue = datum.getFiledValue();
                    String strColumnType = "";
                    //列名匹配后进行列字段匹配
                    if (strColumnNames.indexOf(filedName) != -1) {
                        log.info("列名匹配,获取数据库中该列：" + filedName + "的字段类型");
                        if (mp.containsKey(filedName)) {
                            strColumnType = mp.get(filedName);
                            log.info("map中列" + filedName + "的字段类型为" + strColumnType);
                        } else {
                            log.info("map中列字段不存在时通过工具取并且存储到map中！");
                            strColumnType = tableUtils.getColumnType(rs_main_tb, filedName, log);
                            mp.put(filedName, strColumnType);
                            log.info("获取mp中的值：" + mp.get(filedName));
                        }
                        //添加逻辑：查询当前字段在表中是否为自定义浏览按钮,如果是则转换为其对应的浏览按钮id;Date：2021-05-19
                        if ("1".equals(strCheckBrowser)) {
                            filedvalue = getNewValue(strOaTable, filedName, filedvalue);
                        }
                        log.info("主表-列名：" + filedName + ";字段值：" + filedvalue);
                        strNameSql.append(filedName + ",");
                        if (!"1".equals(strColumnType)) {
                            if ("NUMBER".equals(strColumnType)) {
                                if (isNumeric(filedvalue)) {
                                    strValSql.append("'" + filedvalue + "',");
                                } else {
                                    strResult = "4|number类型的字段不允许存储字符串,如字段：" + filedName + ";数据库类型为：" + strColumnType + ";传入的值为：" + filedvalue + "";
                                    log.info("number类型的字段不允许存储字符串,如字段：" + filedName + ";数据库类型为：" + strColumnType + ";传入的值为：" + filedvalue);
                                    break;
                                }
                            } else {
                                strValSql.append("'" + filedvalue + "',");
                            }
                        } else {
                            strResult = "3|要操作数据的字段类型与数据库不匹配,如字段：" + filedName + ";数据库类型为：" + strColumnType + "";
                            log.info("要操作数据的字段类型与数据库不匹配,如字段：" + filedName + ";数据库类型为：" + strColumnType);
                            break;
                        }
                    } else {
                        log.info("数据的列字段名称与数据库不匹配,请检查是否正确");
                        strResult = "2|数据的列字段名称" + filedName + "与数据库不匹配,请检查是否正确!";
                        break;
                    }
                    log.info("主表-列名：" + filedName + ";列类型：" + strColumnType + ";列的值：" + filedvalue);
                }
                String valSql = strValSql.toString();
                log.info("插入数据sql1:" + valSql);
                valSql = valSql.substring(0, valSql.lastIndexOf(","));
                String nameSql = strNameSql.toString();
                nameSql = nameSql.substring(0, nameSql.lastIndexOf(","));
                log.info("插入数据sql2:" + nameSql);
                String strInsertSql = nameSql + valSql + ")";
                log.info("插入数据最终sql:" + strInsertSql);
                rsTrans.setAutoCommit(false);
                rsTrans.executeUpdate(strInsertSql);
                rsTrans.commit();
                //数据授权
                RecordSet rs = new RecordSet();
                rs.executeQuery("SELECT id FROM(SELECT id FROM " + strOaTable + " ORDER BY id desc)  WHERE ROWNUM=1");
                int maxId = 0;
                while (rs.next()) {
                    maxId = Util.getIntValue(rs.getString("id"));
                }
                log.info("授权id：" + maxId);
                ModeRightInfo modeRightInfoBanc = new ModeRightInfo();
                modeRightInfoBanc.setNewRight(true);
                modeRightInfoBanc.editModeDataShare(1, Integer.parseInt(strFormid), maxId);
            } else {
                log.info("要插入的列数据集合不能为空！");
                strResult = "1|要插入的列数据集合不能为空！";
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            log.info("插入数据出现异常，异常信息：" + ex.toString());
            rsTrans.rollback();
            strResult = "2|插入数据出现异常，异常信息：" + ex.toString() + "";
        } finally {
            return strResult;
        }
    }

    @Override
    public String updateData(String strOaTable, List<filedEntity> data, String strTbkey, ConnStatement rs_main_tb, String strCheckBrowser, Map<String, String> mp) {
        String strResult = "0|操作成功！";
        RecordSetTrans rsTrans = new RecordSetTrans();
        String strUpdateSql = "update " + strOaTable + " set ";
        log.info("传入值data数据：" + data.toString());
        try {
            int dataSize = data.size();
            if (dataSize > 0) {
                String strColumnNames = "";
                for (int i = 1; i <= rs_main_tb.getColumnCount(); i++) {
                    String strName = rs_main_tb.getColumnName(i);
                    strColumnNames += strName + ",";
                }
                if (strColumnNames.length() > 0) {
                    strColumnNames = strColumnNames.substring(0, strColumnNames.lastIndexOf(","));
                }
                log.info("数据库列名为：" + strColumnNames);
                for (filedEntity datum : data) {
                    String filedName = datum.getFiledName().toUpperCase();
                    String filedvalue = datum.getFiledValue();
                    String strColumnType = "";
                    //列名匹配后进行列字段匹配
                    if (strColumnNames.indexOf(filedName) != -1) {
                        log.info("列名匹配,获取数据库中该列：" + filedName + "的字段类型");
                        if (mp.containsKey(filedName)) {
                            strColumnType = mp.get(filedName);
                            log.info("map中列" + filedName + "的字段类型为" + strColumnType);
                        } else {
                            log.info("map中列字段不存在时通过工具取并且存储到map中！");
                            strColumnType = tableUtils.getColumnType(rs_main_tb, filedName, log);
                            mp.put(filedName, strColumnType);
                            log.info("获取mp中的值：" + mp.get(filedName));
                        }

                        //添加逻辑：查询当前字段在表中是否为自定义浏览按钮,如果是则转换为其对应的浏览按钮id;Date：2021-05-19
                        if ("1".equals(strCheckBrowser)) {
                            filedvalue = getNewValue(strOaTable, filedName, filedvalue);
                        }
                        log.info("主表-SAP传入列名(大写格式)为：" + filedName);
                        log.info("主表-列名：" + filedName + ";字段类型：" + strColumnType + ";字段值：" + filedvalue);
                        if (!"1".equals(strColumnType)) {
                            if ("NUMBER".equals(strColumnType)) {
                                if (isNumeric(filedvalue)) {
                                    strUpdateSql += filedName + "=" + filedvalue + ",";
                                } else {
                                    strResult = "3|number类型的字段不允许存储字符串,如字段：" + filedName + ";数据库类型为：" + strColumnType + ";传入的值为：" + filedvalue + "";
                                    log.info("number类型的字段不允许存储字符串,如字段：" + filedName + ";数据库类型为：" + strColumnType + ";传入的值为：" + filedvalue);
                                    break;
                                }
                            } else {
                                strUpdateSql += filedName + "='" + filedvalue + "',";
                            }
                        } else {
                            strResult = "1|要操作数据的字段类型与数据库不匹配,如字段：" + filedName + ";数据库类型为：" + strColumnType + "";
                            log.info("要操作数据的字段类型与数据库不匹配,如字段：" + filedName + ";数据库类型为：" + strColumnType);
                            break;
                        }
                    } else {
                        log.info("数据的列字段名称与数据库不匹配,请检查是否正确");
                        strResult = "2|数据的列字段名称" + filedName + "与数据库不匹配,请检查是否正确!";
                        break;
                    }
                    log.info("列名：" + filedName + ";列类型：" + strColumnType + ";列的值：" + filedvalue);
                }
                log.info("要返回的结果：" + strResult);
                String strCodes = strResult.substring(0, strResult.indexOf("|"));
                if ("0".equals(strCodes)) {
                    log.info("更新语句处理前：" + strUpdateSql);
                    strUpdateSql = strUpdateSql.substring(0, strUpdateSql.lastIndexOf(",")) + " where mainkey='" + strTbkey + "'";
                    log.info("更新数据sql:" + strUpdateSql);
                    rsTrans.setAutoCommit(false);
                    rsTrans.executeQuery(strUpdateSql);
                    rsTrans.commit();
                    log.info("数据更新成功,id为：" + strTbkey);
                }
            } else {
                log.info("要插入的列数据集合不能为空！");
                strResult = "3|要更新的列数据集合不能为空！";
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            log.info("更新数据出现异常,异常信息：" + ex.toString());
            rsTrans.rollback();
            strResult = "4|更新数据出现异常,异常信息：" + ex.toString() + "";
        } finally {
            return strResult;
        }
    }

    @Override
    public String insert_detail(String strTb_detail, List<filedEntity> data, String strTbkey, String strMainKey, ConnStatement rs_detail_tb, String strCheckBrowser, Map<String, String> mp_detail) {
        String strResult = "0|操作成功！";
        SyncUtil syncUtil = new SyncUtil();
        //获取表的formid
        String strFormid = syncUtil.getModeIdBytableName(strTb_detail);
        RecordSetTrans rsTrans = new RecordSetTrans();
        StringBuffer strNameSql = new StringBuffer();
        strNameSql.append("INSERT INTO " + strTb_detail + "(  ");
        strNameSql.append("mainKey,tbKey,formmodeid,modedatacreater,");
        StringBuffer strValSql = new StringBuffer();
        strValSql.append(")VALUES(");
        strValSql.append("'" + strMainKey + "','" + strTbkey + "'," + strFormid + ",2,");
        log.info("传入值data数据：" + data.toString());
        try {
            int dataSize = data.size();
            if (dataSize > 0) {
                String strColumnNames = "";
                for (int i = 1; i <= rs_detail_tb.getColumnCount(); i++) {
                    String strName = rs_detail_tb.getColumnName(i);
                    strColumnNames += strName + ",";
                }
                if (strColumnNames.length() > 0) {
                    strColumnNames = strColumnNames.substring(0, strColumnNames.lastIndexOf(","));
                }
                log.info("数据库列名为：" + strColumnNames);
                for (filedEntity datum : data) {
                    String filedName = datum.getFiledName().toUpperCase();
                    String filedvalue = datum.getFiledValue();
                    strNameSql.append(filedName + ",");
                    String strColumnType = "";
                    //列名匹配后进行列字段匹配
                    if (strColumnNames.indexOf(filedName) != -1) {
                        log.info("列名匹配,获取数据库中该列：" + filedName + "的字段类型");
                        if (mp_detail.containsKey(filedName)) {
                            strColumnType = mp_detail.get(filedName);
                            log.info("mp_detail中列" + filedName + "的字段类型为" + strColumnType);
                        } else {
                            log.info("mp_detail中列字段不存在时通过工具取并且存储到mp_detail中！");
                            strColumnType = tableUtils.getColumnType(rs_detail_tb, filedName, log);
                            mp_detail.put(filedName, strColumnType);
                            log.info("获取mp_detail中的值：" + mp_detail.get(filedName));
                        }
                        //添加逻辑：查询当前字段在表中是否为自定义浏览按钮,如果是则转换为其对应的浏览按钮id;Date：2021-05-19
                        if ("1".equals(strCheckBrowser)) {
                            filedvalue = getNewValue(strTb_detail, filedName, filedvalue);
                        }
                        log.info("明细表-列名：" + filedName + ";字段值：" + filedvalue);
                        if (!"1".equals(strColumnType)) {
                            if ("NUMBER".equals(strColumnType)) {
                                if (isNumeric(filedvalue)) {
                                    strValSql.append(filedvalue + ",");
                                } else {
                                    strResult = "4|number类型的字段不允许存储字符串,如字段：" + filedName + ";数据库类型为：" + strColumnType + ";传入的值为：" + filedvalue + "";
                                    log.info("number类型的字段不允许存储字符串,如字段：" + filedName + ";数据库类型为：" + strColumnType + ";传入的值为：" + filedvalue);
                                    break;
                                }
                            } else {
                                strValSql.append("'" + filedvalue + "',");
                            }
                        } else {
                            strResult = "5|要操作数据的字段类型与数据库不匹配,如字段：" + filedName + ";数据库类型为：" + strColumnType + "";
                            log.info("要操作数据的字段类型与数据库不匹配,如字段：" + filedName + ";数据库类型为：" + strColumnType);
                            break;
                        }
                    } else {
                        log.info("数据的列字段名称与数据库不匹配,请检查是否正确");
                        strResult = "3|数据的列字段名称" + filedName + "与数据库不匹配,请检查是否正确!";
                        break;
                    }
                    log.info("列名：" + filedName + ";列的值：" + filedvalue);
                }
                String valSql = strValSql.toString();
                log.info("插入数据sql1:" + valSql);
                valSql = valSql.substring(0, valSql.lastIndexOf(","));
                String nameSql = strNameSql.toString();
                log.info("插入数据sql2:" + nameSql);
                nameSql = nameSql.substring(0, nameSql.lastIndexOf(","));
                String strInsertSql = nameSql + valSql + ")";
                log.info("插入数据最终sql:" + strInsertSql);
                rsTrans.setAutoCommit(false);
                rsTrans.executeUpdate(strInsertSql);
                rsTrans.commit();
                RecordSet rs = new RecordSet();
                rs.executeQuery("SELECT id FROM(SELECT id FROM " + strTb_detail + " ORDER BY id desc)  WHERE ROWNUM=1");
                int maxId = 0;
                while (rs.next()) {
                    maxId = Util.getIntValue(rs.getString("id"));
                }
                log.info("授权id：" + maxId);
                ModeRightInfo modeRightInfoBanc = new ModeRightInfo();
                modeRightInfoBanc.setNewRight(true);
                modeRightInfoBanc.editModeDataShare(1, Integer.parseInt(strFormid), maxId);
            } else {
                log.info("要插入的列数据为空！");
                strResult = "1|要插入的列数据为空！";
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            log.info("插入数据出现异常，异常信息：" + ex.toString());
            rsTrans.rollback();
            strResult = "2|插入数据出现异常，异常信息：" + ex.toString() + "";
        } finally {
            return strResult;
        }
    }

    @Override
    public String update_detail(String strTb_detail, List<filedEntity> data, String strTbkey, String strMainKey, ConnStatement rs_detail_tb, String strCheckBrowser, Map<String, String> mp_detail) {
        String strResult = "0|操作成功！";
        RecordSetTrans rsTrans = new RecordSetTrans();
        String strUpdateSql = "update " + strTb_detail + " set ";
        log.info("传入值data数据：" + data.toString());
        try {
            int dataSize = data.size();
            if (dataSize > 0) {
                String strColumnNames = "";
                for (int i = 1; i <= rs_detail_tb.getColumnCount(); i++) {
                    String strName = rs_detail_tb.getColumnName(i);
                    strColumnNames += strName + ",";
                }
                if (strColumnNames.length() > 0) {
                    strColumnNames = strColumnNames.substring(0, strColumnNames.lastIndexOf(","));
                }
                log.info("数据库列名为：" + strColumnNames);
                for (filedEntity datum : data) {
                    String filedName = datum.getFiledName().toUpperCase();
                    String filedvalue = datum.getFiledValue();
//                    for (int i=1;i<=rs_main_tb.getColumnCount();i++){
//                        String strName= rs_main_tb.getColumnName(i);
//                        log.info("明细表-SAP传入列名(大写格式)为："+filedName);
//                    }
                    String strColumnType = "";
                    //列名匹配后进行列字段匹配
                    if (strColumnNames.indexOf(filedName) != -1) {
                        strColumnType = tableUtils.getColumnType(rs_detail_tb, filedName, log);
                        if (mp_detail.containsKey(filedName)) {
                            strColumnType = mp_detail.get(filedName);
                            log.info("mp_detail中列" + filedName + "的字段类型为" + strColumnType);
                        } else {
                            log.info("mp_detail中列字段不存在时通过工具取并且存储到mp_detail中！");
                            strColumnType = tableUtils.getColumnType(rs_detail_tb, filedName, log);
                            mp_detail.put(filedName, strColumnType);
                            log.info("获取mp_detail中的值：" + mp_detail.get(filedName));
                        }
                        //添加逻辑：查询当前字段在表中是否为自定义浏览按钮,如果是则转换为其对应的浏览按钮id;Date：2021-05-19
                        if ("1".equals(strCheckBrowser)) {
                            filedvalue = getNewValue(strTb_detail, filedName, filedvalue);
                        }
                        log.info("明细表-列名：" + filedName + ";字段类型：" + strColumnType + ";字段值：" + filedvalue);

                        if (!"1".equals(strColumnType)) {
                            if ("NUMBER".equals(strColumnType)) {
                                if (isNumeric(filedvalue)) {
                                    strUpdateSql += filedName + "=" + filedvalue + ",";
                                } else {
                                    strResult = "3|number类型的字段不允许存储字符串,如字段：" + filedName + ";数据库类型为：" + strColumnType + ";传入的值为：" + filedvalue + "";
                                    log.info("number类型的字段不允许存储字符串,如字段：" + filedName + ";数据库类型为：" + strColumnType + ";传入的值为：" + filedvalue);
                                    break;
                                }
                            } else {
                                strUpdateSql += filedName + "='" + filedvalue + "',";
                            }
                        } else {
                            strResult = "1|要操作数据的字段类型与数据库不匹配,如字段：" + filedName + ";数据库类型为：" + strColumnType + "";
                            log.info("要操作数据的字段类型与数据库不匹配,如字段：" + filedName + ";数据库类型为：" + strColumnType);
                            break;
                        }
                    } else {
                        log.info("数据的列字段名称与数据库不匹配,请检查是否正确");
                        strResult = "2|数据的列字段名称" + filedName + "与数据库不匹配,请检查是否正确";
                        break;
                    }
                    log.info("列名：" + filedName + ";列类型：" + strColumnType + ";列的值：" + filedvalue);
                }
                String strCodes = strResult.substring(0, strResult.indexOf("|"));
                if ("0".equals(strCodes)) {
                    log.info("更新语句处理前：" + strUpdateSql);
                    strUpdateSql = strUpdateSql.substring(0, strUpdateSql.lastIndexOf(",")) + " where mainkey='" + strMainKey + "' and tbKey='" + strTbkey + "'";
                    log.info("更新数据sql:" + strUpdateSql);
                    rsTrans.setAutoCommit(false);
                    rsTrans.executeQuery(strUpdateSql);
                    rsTrans.commit();
                    log.info("数据更新成功,id为：" + strTbkey);
                }
            } else {
                log.info("明细list集合数据为空！");
                strResult = "3|明细list集合数据为空";
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            log.info("更新数据出现异常,异常信息：" + ex.toString());
            rsTrans.rollback();
            strResult = "4|更新数据出现异常,异常信息：" + ex.toString() + "";
        } finally {
            return strResult;
        }
    }

    @Override
    public String delete_Data(String strOaTable, String strTbkey, String strMainKey) {
        String strResult = "0|操作成功！";
        RecordSetTrans rsTrans = new RecordSetTrans();
        String strDelSql = "delete " + strOaTable + " where ";
        Boolean isDel = true;
        if (strTbkey.length() > 0 && !"".equals(strMainKey)) {
            //删除明细表
            strDelSql += "mainKey='" + strMainKey + "' and tbKey in('" + strTbkey + "')";
        } else if (strTbkey.length() > 0) {
            //删除主表
            strDelSql += "mainKey in('" + strTbkey + "')";
        } else {
            strResult = "1|没有找到要删除的表,请检查主键是否填写";
            isDel = false;
        }
        log.info("要删除的sql语句：" + strDelSql);
        if (isDel) {
            try {
                rsTrans.setAutoCommit(false);
                rsTrans.executeQuery(strDelSql);
                rsTrans.commit();
            } catch (Exception ex) {
                ex.printStackTrace();
                rsTrans.rollback();
                strResult = "2|删除数据异常,异常消息：" + ex.toString();
            }
        }
        return strResult;
    }

    @Override
    public String insert_main_new(String strOaTable, String strTbkey, Map<String, String> mp, Map<String, String> mp_Sql) {
        String strResult = "";
        SyncUtil syncUtil = new SyncUtil();
        //获取表的formid
        String strFormid = syncUtil.getModeIdBytableName(strOaTable);
        StringBuffer strNameSql = new StringBuffer();
        strNameSql.append("INSERT INTO " + strOaTable + "(  ");
        strNameSql.append("mainKey,formmodeid,modedatacreater,");
        StringBuffer strValSql = new StringBuffer();
        strValSql.append(")VALUES(");
        strValSql.append("'" + strTbkey + "'," + strFormid + ",2,");
        try {
            String valSql = strValSql.append(mp_Sql.get("strValSql")).toString();
//            log.info("主表-插入数据sql1:" + valSql);
            valSql = valSql.substring(0, valSql.lastIndexOf(","));
            String nameSql = strNameSql.append(mp_Sql.get("strNameSql")).toString();
            nameSql = nameSql.substring(0, nameSql.lastIndexOf(","));
//            log.info("主表-插入数据sql2:" + nameSql);
            strResult = nameSql + valSql + ")";
//            rsTrans.setAutoCommit(false);
//            rsTrans.executeUpdate(strInsertSql);
//            rsTrans.commit();
//            //数据授权
//            RecordSet rs = new RecordSet();
//            rs.executeQuery("SELECT id FROM(SELECT id FROM " + strOaTable + " ORDER BY id desc)  WHERE ROWNUM=1");
//            int maxId = 0;
//            while (rs.next()) {
//                maxId = Util.getIntValue(rs.getString("id"));
//            }
//            log.info("授权id：" + maxId);
//            ModeRightInfo modeRightInfoBanc = new ModeRightInfo();
//            modeRightInfoBanc.setNewRight(true);
//            modeRightInfoBanc.editModeDataShare(1, Integer.parseInt(strFormid), maxId);
        } catch (Exception ex) {
            ex.printStackTrace();
            log.info("主表-插入数据sql拼接出现异常，异常信息：" + ex.toString());
        } finally {
            return strResult;
        }

    }

    @Override
    public String update_main_new(String strOaTable, String strTbkey, Map<String, String> mp, Map<String, String> mp_Sql) {
        String strUpdateSql = "";
        try {
            strUpdateSql = "update " + strOaTable + " set ";
            strUpdateSql += mp_Sql.get("strValSql");
            strUpdateSql = strUpdateSql.substring(0, strUpdateSql.lastIndexOf(",")) + " where mainkey='" + strTbkey + "'";
//            log.info("主表-更新数据sql:" + strUpdateSql);
        } catch (Exception ex) {
            ex.printStackTrace();
            log.info("主表-更新数据sql拼接异常,异常信息：" + ex.toString());
        } finally {
            return strUpdateSql;
        }
    }

    @Override
    public String insert_detail_new(String strTb_detail, String strTbkey, String strMainKey, Map<String, String> mp, Map<String, String> mp_Sql) {
        String strResult = "";
        SyncUtil syncUtil = new SyncUtil();
        //获取表的formid
        String strFormid = syncUtil.getModeIdBytableName(strTb_detail);
        StringBuffer strNameSql = new StringBuffer();
        strNameSql.append("INSERT INTO " + strTb_detail + "(  ");
        strNameSql.append("mainKey,tbKey,formmodeid,modedatacreater,");
        StringBuffer strValSql = new StringBuffer();
        strValSql.append(")VALUES(");
        strValSql.append("'" + strMainKey + "','" + strTbkey + "'," + strFormid + ",2,");
        try {
            String valSql = mp_Sql.get("strValSql");
//            log.info("明细表-插入数据sql1:" + valSql);
            valSql = valSql.substring(0, valSql.lastIndexOf(","));
            String nameSql = strNameSql.toString();
//            log.info("明细表-插入数据sql2:" + nameSql);
            nameSql = nameSql.substring(0, nameSql.lastIndexOf(","));
            strResult = nameSql + valSql + ")";
//            log.info("明细表-插入数据最终sql:" + strInsertSql);

        } catch (Exception ex) {
            ex.printStackTrace();
            log.info("插入明细表数据sql拼接出现异常，异常信息：" + ex.toString());
        } finally {
            return strResult;
        }
    }

    @Override
    public String update_detail_new(String strTb_detail, String strTbkey, String strMainKey, Map<String, String> mp, Map<String, String> mp_Sql) {
        String strResult = "";
        String strUpdateSql = "update " + strTb_detail + " set ";
        try {
            strUpdateSql += mp_Sql.get("strValSql");
            strUpdateSql = strUpdateSql.substring(0, strUpdateSql.lastIndexOf(",")) + " where mainkey='" + strMainKey + "' and tbKey='" + strTbkey + "'";
//            log.info("明细表-更新数据sql:" + strUpdateSql);
            strResult = strUpdateSql;
        } catch (Exception ex) {
            ex.printStackTrace();
            log.info("明细表-更新数据拼接sql出现异常,异常信息：" + ex.toString());
        } finally {
            return strResult;
        }
    }

    @Override
    public messageVo check_Data(List<filedEntity> data, String strColumnNames, ConnStatement statement, String strCheckBrowser, Map<String, String> mp, String strOaTable, Map<String, String> mp_sql, String op_type) {
        messageVo message = new messageVo();
        message.setStatus("0");
        message.setMessage("列字段校验成功！");
        StringBuffer strNameSql = new StringBuffer();
        StringBuffer strValSql = new StringBuffer();
        String regex = "^[0-9]|(\\.\\d+)+(\\-)";
        int dataSize = data.size();
        try {
            if (dataSize > 0) {
                for (filedEntity datum : data) {
                    String filedName = datum.getFiledName().toUpperCase();
                    String filedvalue = datum.getFiledValue();
                    String strColumnType = "";
                    if (strColumnNames.indexOf(filedName) != -1) {
                        if (mp.containsKey(filedName)) {
                            strColumnType = mp.get(filedName);
                            log.info("map中列" + filedName + "的字段类型为" + strColumnType);
                        } else {
                            log.info("map中列字段不存在时通过工具取并且存储到map中！");
                            strColumnType = tableUtils.getColumnType(statement, filedName, log);
                            if (strColumnType.equals("NUMBER")) {
                                //将数字类型的字段及值存储到map集合中
                                mp.put(filedName, strColumnType);
                                log.info("获取mp中的值：" + mp.get(filedName));
                            }
                        }
                        if (strColumnType.equals("NUMBER")) {
                            if (filedvalue.matches(regex)) {
                                log.info("转换数字中包含的特殊字符");
                                if (filedvalue.indexOf("-") != -1) {
                                    filedvalue = "-" + filedvalue.substring(0, filedvalue.length() - 1);
                                }
                            } else {
                                message.setStatus("1");
                                message.setMessage("number类型的字段不允许存储字符串,如字段：" + filedName + ";数据库类型为：" + strColumnType + ";传入的值为：" + filedvalue + "");
                                log.info("number类型的字段不允许存储字符串,如字段：" + filedName + ";数据库类型为：" + strColumnType + ";传入的值为：" + filedvalue);
                                break;
                            }
                        }
//                        //添加逻辑：查询当前字段在表中是否为自定义浏览按钮,如果是则转换为其对应的浏览按钮id;Date：2021-05-19
                        if ("1".equals(strCheckBrowser)) {
                            log.info("判断是否是浏览按钮："+filedName);
                            filedvalue = getNewValue(strOaTable, filedName, filedvalue);
                        }
                        log.info("主表-列名：" + filedName + ";字段值：" + filedvalue);
                        strNameSql.append(filedName + ",");

                        if (op_type.equals("insert")) {
                            strValSql.append("'" + filedvalue + "',");
                        } else {
                            strValSql.append(filedName + "='" + filedvalue + "',");
                        }
                        mp_sql.put("strNameSql", strNameSql.toString());
                        mp_sql.put("strValSql", strValSql.toString());

                    } else {
                        log.info("数据的列字段名称与数据库不匹配,请检查是否正确");
                        message.setStatus("3");
                        message.setMessage("数据的列字段名称" + filedName + "与数据库不匹配,请检查是否正确!");
                        break;
                    }
                    log.info("主表-列名：" + filedName + ";列类型：" + strColumnType + ";列的值：" + filedvalue);
                }
            } else {
                log.info("要插入的列数据集合不能为空！");
                message.setStatus("4");
                message.setMessage("要插入的列数据集合不能为空！");
            }
        } catch (Exception ex) {
            log.info("列字段校验异常,异常信息为：" + ex.toString());
            message.setStatus("5");
            message.setMessage("要插入的列数据集合不能为空！");
        } finally {
            return message;
        }

    }

    /***
     * 获取浏览按钮转化后的值
     * @param strOaTable
     * @param filedName
     * @param filedvalue
     * @return
     */
    private String getNewValue(String strOaTable, String filedName, String filedvalue) {
        RecordSet rs = new RecordSet();
        String result = filedvalue;
        try {
            String strDetailTable = "";
            if (strOaTable.toUpperCase().contains("_DT")) {//明细表
                strDetailTable = strOaTable;
                strOaTable = strOaTable.substring(0, strOaTable.toUpperCase().indexOf("_DT"));
            }
            String strQuery = "select id from WORKFLOW_BILL where TABLENAME='" + strOaTable + "'";

            log.info("查询建模表表单id的Sql:" + strQuery);
            rs.execute(strQuery);
            Integer bill_id = 0;
            while (rs.next()) {
                bill_id = Integer.valueOf(Util.null2String(rs.getString("id"), "0"));
            }
            if (bill_id != 0) {
                String strIsBrowserSql = "SELECT SQLTEXT1,SQLTEXT2 FROM OAUSER.MODE_BROWSER WHERE SHOWNAME = (SELECT SUBSTR(FIELDDBTYPE, 9) FROM oauser.WORKFLOW_BILLFIELD where  billid = '" + bill_id + "' and UPPER(fieldname) = '" + filedName + "'";
                if (!"".equals(strDetailTable)) {
                    strIsBrowserSql += " and detailtable is not null and rownum=1)";
                } else {
                    strIsBrowserSql += " and detailtable is null and rownum=1)";
                }
                log.info("查询字段是否是浏览按钮sql：" + strIsBrowserSql);
                RecordSet rs_browser_filed = new RecordSet();
                rs_browser_filed.execute(strIsBrowserSql);
                RecordSet rs_temp = new RecordSet();
                String str_temp = "";
                if (rs_browser_filed.getCounts() > 0) {
                    while (rs_browser_filed.next()) {
                        //如果该字段为浏览按钮时，转换值
                        str_temp = Util.null2String(rs_browser_filed.getString("SQLTEXT2"));
                    }
                    log.info("查询浏览按钮" + filedName + "对应源数据的sql：" + str_temp);
                    if (!str_temp.equals("")) {
                        String strNewSql = str_temp.replace("like ?", "like'%" + filedvalue + "%'").replace("id=?", "id='" + filedvalue + "'");
                        log.info("对应源数据的sql拼接后为：" + strNewSql);
                        rs_temp.execute(strNewSql);
                    }
                    log.info("浏览按钮转换之前的值为：" + result);
                    while (rs_temp.next()) {
                        result = Util.null2String(rs_temp.getString(1));
                    }
                    log.info("浏览按钮" + filedName + "转换之后的值为：" + result);
                    return result;
                } else {
                    //如果不为浏览按钮时,原值输出
                    return result;
                }
            } else {
                return result;
            }
        } catch (Exception ex) {
            log.info("查询浏览按钮出现异常，异常信息为：" + ex.toString());
            return result;
        }
    }

    public String getColumnNames(ConnStatement statement) {
        String strColumnNames = "";
        try {
            for (int i = 1; i <= statement.getColumnCount(); i++) {
                String strName = statement.getColumnName(i);
                strColumnNames += strName + ",";
            }
            if (strColumnNames.length() > 0) {
                strColumnNames = strColumnNames.substring(0, strColumnNames.lastIndexOf(","));
            }
            log.info("数据库列名为：" + strColumnNames);
        } catch (Exception ex) {
            log.info("获取数据库列名异常,异常信息：" + ex.toString());
        } finally {
            return strColumnNames;
        }
    }

}
